Supported Data Types

Retrieving and sending certain data types can be expensive. Advances in processor technology brought significant improvements to the way that operations such as floating-point math are handled; however, retrieving and sending certain data types are still expensive when the active portion of your application will not fit into on-chip cache. When you are working with data on a large scale, it is still important to select the data type that can be processed most efficiently.

For example, integer data is processed faster than floating-point data. Floating-point data is defined according to internal database-specific formats, usually in a compressed format. The data must be decompressed and converted into a different format so that it can be processed by the wire protocol.

Processing time is shortest for character strings, followed by integers, which usually require some conversion or byte ordering. Processing floating-point data and timestamps is at least twice as slow as integers.

DB2 UDB on z/OS

The following table lists the data types that the ODBC driver supports for DB2 UDB on z/OS:

Table B-1. Mapping Data Types for DB2 UDB on z/OS to ODBC Data Types  

DB2 UDB Data Type	ODBC Data Type (ANSI)
BIGINT	SQL_BIGINT
BLOB	SQL_LONGVARBINARY
CHAR(size)	SQL_CHAR
CHAR(size) FOR BIT DATA	SQL_BINARY
CLOB	SQL_LONGVARCHAR
DATE	SQL_DATE
DBCLOB	SQL_WLONGVARCHAR
DECIMAL	SQL_DECIMAL
FLOAT	SQL_FLOAT SQL_DOUBLE
INTEGER	SQL_INTEGER
LONGVARCHAR	SQL_LONGVARCHAR
LONGVARBINARY	SQL_LONGVARBINARY
NUMERIC	SQL_NUMERIC
REAL	SQL_REAL
SMALLINT	SQL_SMALLINT
TIME	SQL_TIME
TIMESTAMP	SQL_TIMESTAMP1 SQL_CHAR2
VARCHAR(size)	SQL_VARCHAR
VARCHAR(size) FOR BIT DATA	SQL_VARBINARY

1Only present when DataSourceFetchTimestampAsString=False. 2Only present when DataSourceFetchTimestampAsString=True.

Table B-2. Mapping Data Types for DB2 UDB for z/OS to ADO Data Types  

DB2 UDB Data Type	OLE DB Data Type
BIGINT	DBTYPE_I8
BLOB	DBTYPE_BYTES
CHAR(size)	DBTYPE_STR
CHAR(size) FOR BIT DATA	DBTYPE_BYTES
CLOB	DBTYPE_STR
DATE	DBTYPE_DBDATE
DECIMAL	DBTYPE_DECIMAL
FLOAT	DBTYPE_R8
INTEGER	DBTYPE_I4
LONG VARCHAR	DBTYPE_STR
LONG VARCHAR FOR BIT DATA	DBTYPE_BYTES
NUMERIC	DBTYPE_NUMERIC
REAL	DBTYPE_R4
SMALLINT	DBTYPE_I2
TIME	DBTYPE_DBTIME
TIMESTAMP	DBTYPE_DBTIMESTAMP1 DBTYPE_STR2
VARCHAR(size)	DBTYPE_STR
VARCHAR(size) FOR BIT DATA	DBTYPE_BYTES

1Only present when DataSourceFetchTimestampAsString=False. 2Only present when DataSourceFetchTimestampAsString=True.

DB2 UDB on Linux, UNIX, and Windows

The following table lists the data types that the ODBC driver DB2 UDB on Linux, UNIX, and Windows :

Table B-3. Mapping Data Types for DB2 UDB on Linux/UNIX/Windows to ODBC Data Types  

DB2 SQL Data Type	ODBC Data Types (Non-Unicode Service)	ODBC Data Types (Unicode Service)
BIGINT	SQL_BIGINT	SQL_BIGINT
BLOB1	SQL_LONGVARBINARY	SQL_LONGVARBINARY
CHAR(size)	SQL_CHAR	SQL_CHAR2 SQL_WCHAR
CHAR(size) FOR BIT DATA	SQL_BINARY	SQL_BINARY
CLOB1	SQL_LONGVARCHAR	SQL_WLONGVARCHAR3
DATE	SQL_DATE	SQL_DATE
DBCLOB4	Not supported	SQL_WLONGVARCHAR
DECIMAL	SQL_DECIMAL	SQL_DECIMAL
FLOAT	SQL_DOUBLE SQL_FLOAT	SQL_DOUBLE SQL_FLOAT
GRAPHIC(size)4	Not supported	SQL_WCHAR
INTEGER	SQL_INTEGER	SQL_INTEGER
LONG VARCHAR1	SQL_LONGVARCHAR	SQL_LONGVARCHAR2
LONG VARCHAR FOR BIT DATA1	SQL_LONGVARBINARY	SQL_LONGVARBINARY
LONG VARGRAPHIC4	Not supported	SQL_WLONGVARCHAR
NUMERIC	SQL_NUMERIC	SQL_NUMERIC
REAL	SQL_REAL	SQL_REAL
SMALLINT	SQL_SMALLINT	SQL_SMALLINT
TIME	SQL_TIME	SQL_TIME
TIMESTAMP	SQL_TIMESTAMP SQL_CHAR5	SQL_TIMESTAMP SQL_CHAR5
VARCHAR(size)	SQL_VARCHAR	SQL_VARCHAR2 SQL_WVARCHAR
VARCHAR(size) FOR BIT DATA	SQL_VARBINARY	SQL_VARBINARY
VARGRAPHIC(size)4	Not supported	SQL_WVARCHAR

1Depends on setting of the DataSourceLobAsLongvar service attribute. When set to False, LOB data types will be handled as LOB objects; when set to True, LOB data types are handled as Longvar data types. 2If DataSourceReportChar is enabled, the CHAR, VARCHAR, LONGVARCHAR data types are additionally reported as the non-Unicode data types (see previous table) 3Depending on DataSourceReportLobAsLongVarchar: if set to TRUE: LONGVARCHAR data types are reported; if set to FALSE: LOB data types are reported. 4If DataSourceDB2GraphicSupport is enabled, the GRAPHIC datatypes will be available. 5DataSourceFetchTimestampAsString specifies whether timestamps are fetched as character strings.

Informix

The following table lists the data types that the ODBC driver supports for Informix:

Table B-4. Mapping Informix Data Types to ODBC Data Types 

Informix Data Type	ODBC Data Type
BLOB	SQL_LONGVARBINARY
BOOLEAN	SQL_BIT
BYTE	SQL_LONGVARBINARY
CHAR	SQL_CHAR
CLOB	SQL_LONGVARCHAR
DATE	SQL__TYPE_DATE
DATETIME HOUR TO SECOND	SQL__TYPE_TIME
DATETIME YEAR TO FRACTION(5)	SQL__TYPE_TIMESTAMP
DECIMAL	SQL_DECIMAL
FLOAT	SQL_FLOAT
INTEGER	SQL_INTEGER
INT8	SQL_BIGINT
LVARCHAR	SQL_LONGVARCHAR
MONEY	SQL_DECIMAL
NUMERIC	SQL_NUMERIC
SERIAL	SQL_INTEGER
SERIAL8	SQL_BIGINT
SMALLFLOAT	SQL_REAL
SMALLINT	SQL_SMALLINT
TEXT	SQL_LONGVARCHAR
VARCHAR	SQL_VARCHAR

Microsoft SQL Server

The following table maps the ODBC data types that the ODBC driver supports to the Microsoft SQL Server:

Table B-5. Mapping Data Types for Microsoft SQL Server to ODBC Data Types 

Microsoft SQL Server 2000, 2005 Data Type	ODBC Data Type
bigint	SQL_BIGINT
bigint identity	SQL_BIGINT
binary	SQL_BINARY
bit	SQL_BIT
char	SQL_CHAR1 SQL_WCHAR2
datetime	SQL_DATETIME3 SQL_CHAR4
decimal	SQL_DECIMAL
decimal identity	SQL_DECIMAL
float	SQL_FLOAT
guid	SQL_BINARY
image	SQL_LONGVARBINARY
int	SQL_INTEGER
int identity	SQL_INTEGER
money	SQL_DECIMAL
nchar	SQL_CHAR1 SQL_WCHAR2
ntext	SQL_LONGVARCHAR1 SQL_WLONGVARCHAR2
nvarchar(max)	SQL_LONGVARCHAR
numeric	SQL_NUMERIC
numeric identity	SQL_NUMERIC
nvarchar	SQL_VARCHAR1 SQL_WVARCHAR2
real	SQL_REAL
smalldatetime	SQL_DATETIME3 SQL_CHAR4
smallint	SQL_SMALLINT
smallint identity	SQL_SMALLINT
smallmoney	SQL_DECIMAL
SQLVariant	SQL_BINARY
sysname	SQL_VARCHAR1 SQL_WVARCHAR2
text	SQL_LONGVARCHAR1 SQL_WVARCHAR2
timestamp	SQL_BINARY
tinyint	SQL_TINYINT
tinyint identity	SQL_TINYINT
uniqueidentifier	SQL_BINARY
varbinary	SQL_VARBINARY
varbinary(max)	SQL_LONGVARBINARY
varchar	SQL_VARCHAR1 SQL_WVARCHAR2
varchar(max)	SQL_LONGVARCHAR1

1Not present when DataSourceReportChar=False and ServiceCodePage=Database 2Only present whe ServiceCodePage=Database 3Only present when DataSourceFetchTimestampAsString=False 4Only present when DataSourceFetchTimestampAsString=True

Oracle

The following table lists the data types that the ODBC driver supports for Oracle:

Table B-6. Mapping the Oracle Data Types to ODBC Data Types 

Oracle 9i Data Type	Oracle10g Data Type	ODBC Data Type
BFILE	BFILE	SQL_LONGVARBINARY
	BINARY_FLOAT	SQL_REAL
	BINARY_DOUBLE	SQL_DOUBLE
BLOB	BLOB	SQL_LONGVARBINARY
CHAR(size)	CHAR(size)	SQL_CHAR1 SQL_WCHAR2
CLOB	CLOB	SQL_LONGVARCHAR3 SQL_WLONGVARCHAR2
DATE	DATE	SQL_TYPE_TIMESTAMP
LONG	LONG	SQL_LONGVARCHAR1 SQL_WLONGVARCHAR2
LONG RAW	LONG RAW	SQL_LONGVARBINARY
NCHAR(size)	NCHAR(size)	SQL_CHAR4 SQL_WCHAR5
NCLOB	NCLOB	SQL_LONGVARCHAR4 SQL_WLONGVARCHAR5
NUMBER	NUMBER	SQL_FLOAT
NUMBER(p,s)	NUMBER(p,s)	SQL_DECIMAL
NVARCHAR2(size)	NVARCHAR2(size)	SQL_VARCHAR4 SQL_WVARCHAR5
RAW(size)	RAW(size)	SQL_VARBINARY
ROWID	ROWID	SQL_VARCHAR
TIMESTAMP	TIMESTAMP	SQL_TIMESTAMP6 SQL_CHAR7
TIMESTAMP WITH LOCAL TIME ZONE	TIMESTAMP WITH LOCAL TIME ZONE	SQL_TYPE_TIMESTAMP6 SQL_CHAR7
TIMESTAMP WITH TIME ZONE	TIMESTAMP WITH TIME ZONE	SQL_TYPE_TIMESTAMP6, 8, 9
TIMESTAMP WITH TIME ZONE	TIMESTAMP WITH TIME ZONE	SQL_CHAR7, 8
VARCHAR2(size)		SQL_VARCHAR1
VARCHAR2(size)		SQL_WVARCHAR2

1Not present when DataSourceReportChar=False, ServiceCodePage=Database and database character set is Unicode 2Only present when ServiceCodePage=Database and database character set is Unicode 3Not present when ServiceCodePage=Database and database character set is Unicode 4Not present when ServiceCodePage=Database and database character set is Unicode 5Only present when ServiceCodePage=Database and national character set is Unicode 6Only present when DataSourceFetchTimestampAsString=False 7Only present when DataSourceFetchTimestampAsString=True 8Only present when DataSourceORAMapTSWTZ=True 9The time zone value is not accessible (or can not be set). Instead, the client time zone is used either to pass to Oracle or to convert from the stored time zone value.

Sybase

The following table lists the data types that the ODBC driver supports for Sybase:

Table B-7. Mapping the Data Types for Sybase to ODBC Data Types  

Sybase Data Type	ODBC Data Type
BIGINT	SQL_BIGINT
BINARY	SQL_BINARY
BIT	SQL_BIT
CHAR	SQL_CHAR1 SQL_WCHAR2
DATETIME	SQL_TIMESTAMP3 SQL_CHAR4
DECIMAL	SQL_DECIMAL
FLOAT	SQL_FLOAT
IMAGE	SQL_LONGVARBINARY
INT	SQL_INTEGER
MONEY	SQL_DECIMAL
NUMERIC	SQL_NUMERIC
REAL	SQL_REAL
SMALLDATETIME	SQL_TIMESTAMP3 SQL_CHAR4
SMALLINT	SQL_SMALLINT
SMALLMONEY	SQL_DECIMAL
SYSNAME	SQL_VARCHAR5 SQL_WVARCHAR2
TEXT	SQL_LONGVARCHAR1 SQL_WLONGVARCHAR2
TIMESTAMP	SQL_BINARY
TINYINT	SQL_TINYINT
UBIGINT	SQL_DECIMAL
UNICHAR	SQL_WCHAR6
UNIVARCHAR	SQL_WVARCHAR6
VARBINARY	SQL_VARBINARY
VARCHAR	SQL_VARCHAR1 SQL_WVARCHAR2

1Not present when DataSourceReportChar=False and ServiceCodePage=Database 2Only present when ServiceCodePage=Database 3Only present when DataSourceFetchTimestampAsString=False 4Only present when DataSourceFetchTimestampAsString=True 5Not present when ServiceCodePage=Database 6Only present when ServiceCodePage=Database and database character set is UTF-8